---
title: "Task_5 Dashboard"
output: 
  flexdashboard::flex_dashboard:
    vertical_layout: scroll
    source_code: embed
    theme: cerulean
---

Calculations/Tables 
=====================================  


```{r setup, include=FALSE}
library(flexdashboard)
```


##Loading Required Libraries
```{r}
library(gsheet)#Access data from docs
library(dlookr)
library(dplyr)
library(ggplot2)
library(forcats)
library(magrittr)
library(treemap)
library(tm)
library(SnowballC)
library(wordcloud)
library(RColorBrewer)
library(leaflet)
library(widgetframe)
library(doBy)
library(viridisLite)
library(plotly)
```

###Downloading Dataset
```{r}
superstore <- gsheet2tbl('https://docs.google.com/spreadsheets/d/1ELHYd5wKnLdJE3tuszI-Ced_hyye5CnOnArshR_UGPI/edit?usp=sharing') 
str(superstore)
```

**Exploratory Data Analysis**

*Data Description*
Data contains Sales records from a Super-Store located in USA
Ship Mode- The mode in which the product was shipped.
            4 Possible Modes
                "First Class" "Same Day"  "Second Class" "Standard Class"
            
*Segment- The Sale segment
          +3 Possible Segments
            +"Consumer"    "Corporate"   "Home Office"

*Country- Country in which the sale was made. (United States)

*City- The city in which the sale was made .

*State- State in which the sale was made.

*Postal Code- Postal Code for the region of the sale.

*Region - The Region in which the sale was made.
          +4 possible values
           + "South"   "West"    "Central" "East"
*Category- The category of product which was purchased.
            +3 possible values
              +"Furniture" "Office Supplies" "Technology"
*Sub Category- A more in-depth category of update
                +17 possible values
                
*Sales- Amount of money transaction for the Sale

*Quantity- Quantity of products involved in the sale

*Discount- Discount applied on the sale

*Profit- Profit made from the sale


**Describe the data** 
```{r}
describe(superstore,Discount,Quantity,Profit,Sales)

```

**Overview of the data**
```{r}
Overview<-superstore %>% summarise(Sale=sum(Sales),Profit=sum(Profit))
Overview

```

**Number of Sales**
```{r}
#Get the number of sales made in each state to find useful information 
No_sales<-superstore %>% group_by(State) %>% summarise(N=n(),profit=mean(Profit)) %>% arrange(desc(N)) %>% head()
No_sales

#Total Sales made:
nrow(superstore)

```



**Breakdown of Number of Sales**
*State-Wise sale % contribution*
```{r}
#State-Wise sale % contribution
superstore %>% group_by(State) %>% summarise(PerCent=n()*100/nrow(superstore)) %>% arrange(desc(PerCent))
```

*City-Wise sale % Contribution*
```{r}
#City-Wise sale % Contribution
superstore %>% group_by(City) %>% summarise(PerCent=n()*100/nrow(superstore)) %>% arrange(desc(PerCent))
```


**Profit Breakdown**
```{r}
#Find the states that are most profitable
superstore %>% group_by(State) %>% summarise(Total_Profit=sum(Profit),SD=sd(Profit)) %>% arrange(desc(Total_Profit))

```


**Avg-Discounts (State Wise)**
```{r}

dis<-superstore %>% group_by(State) %>% summarise(Avg=mean(Discount)) %>% arrange(desc(Avg)) %>% head()
dis

superstore %>% group_by(State) %>% filter(State%in%dis$State) %>% summarise(Total=sum(Sales))

sales<-superstore %>% group_by(State) %>% summarise(Total=sum(Sales)) %>% arrange(desc(Total))

#Find the states which bring the most amount of money in sales
valued_states<-sales$State[1:20]

```



**Which Category of Sales brings the maximum sales and profits? **
```{r}
superstore %>% group_by(Category) %>% summarise(Total_Sale=sum(Sales),Total_Profit=sum(Profit))

superstore %>% group_by(State) %>% filter(Category=="Technology") %>% summarise(N=n()) %>% arrange(desc(N))
#We see California pops up again as the biggest customer 

```


**Number of Sales for each Category**
```{r}
superstore %>% group_by(Category) %>% summarise(n())

```

**In more Detail**

```{r}
Sold_Category<-superstore %>% group_by(State,Category) %>% summarise(N=n()) %>% arrange(desc(N))
Sold_Category

```

**Check what % of sales were discounted for big sale spots (States)**
```{r}

superstore %>% group_by(State) %>% filter(State%in%valued_states) %>%
  summarise(T_S=sum(Sales),T_D=sum(Discount),T_P=sum(Profit),Percent=T_D*100/T_S) %>% arrange(desc(T_P))

```



Representations {data-orientation=rows}
===================================== 

Row
-------------------------------------
    
### Sale Hot-Spots
    
```{r}
cities<-data.frame(S=c("California","New York","Texas","Pennsylvania"),lat=c(35.8863,43.1007,29.4128,41.2033),lng=c(-118.656,-75.2932,-94.9658,-77.1945))
cities<-setNames(cities,c("State","lat","lng"))
temp<-merge(cities,No_sales)[1:4,1:4]

map <- leaflet() %>%
  setView(lat = 36, lng = -99.7129, zoom=4) %>%
  addTiles(group="OSM") %>%
  addProviderTiles("Esri.NatGeoWorldMap") %>%
  addCircleMarkers(data =temp, ~lng, ~lat,weight = 0.5, col = 'black',fillColor = "purple", radius =~ N/100 , fillOpacity = 0.6, stroke = T, label = ~paste0(as.character(State),'  ',as.character(N)),group = 'Points')
map
```
   
Row
-------------------------------------
    
### Treemap
    
```{r}
treemap(superstore,index=c("Category","Sub-Category"),vSize = "Sales",vColor = "Profit",type = "value",palette = rev(viridis(6)))
```
    
### WordCloud

```{r}
wordcloud(words = superstore$`Sub-Category`,min.freq = 1,max.words = 100,random.order = F,rot.per = 0.35,colors = brewer.pal(8,'Set2'))
```

Number of Sales {data-orientation=rows}
===================================== 

Row {data-width=650}
-------------------------------------

### State Wise
```{r}
t<-superstore %>% group_by(State) %>% summarise(N=n(),profit=mean(Profit)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$State[1:6],
    y = t$N[1:6],
    type = 'bar',
    text = t$State[1:6],
    marker=list(color=c("red","red","silver","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'State-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'State'
    ),
    yaxis = list(
      title = '# of Sales'
    )
  ) 

fig 
```

Row {.tabset .tabset-fade}
-------------------------------------
   
### City Wise

```{r}
t<-superstore %>% group_by(City) %>% summarise(N=n(),profit=mean(Profit)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$City[1:6],
    y = t$N[1:6],
    type = 'bar',
    text = t$State[1:6],
    marker=list(color=c("red","red","silver","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'City-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'City'
    ),
    yaxis = list(
      title = '# of Sales'
    )
  ) 

fig 
```   
 
### Region Wise
    
```{r}
t<-superstore %>% group_by(Region) %>% summarise(N=n()) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$Region,
    y = t$N,
    type = 'bar',
    text = t$Region,
    marker=list(color=c("red","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'Region-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'Region'
    ),
    yaxis = list(
      title = '# of Sales'
    )
  ) 

fig 
```

### Ship-Mode Wise
    
```{r}
t<-superstore %>% group_by(`Ship Mode`) %>% summarise(N=n()) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$`Ship Mode`,
    y = t$N,
    type = 'bar',
    text = t$`Ship Mode`,
    marker=list(color=c("red","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'Ship_Mode-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'Ship Mode'
    ),
    yaxis = list(
      title = '# of Sales'
    )
  ) 

fig 
```

### Segment Wise
    
```{r}
data<-superstore %>% group_by(Segment) %>% summarise(N=n())
fig <- plot_ly(data, labels = ~Segment, values = ~N, type = 'pie',
               textposition = 'inside',
        textinfo = 'label+percent',
        insidetextfont = list(color = '#FFFFFF'),
        hoverinfo = 'text',
        text = ~paste( N, ' Sales'),showlegend = FALSE,
        marker=list(line = list(color = '#FFFFFF', width = 1)))

fig <- fig %>% layout(title = '# of Sales Segment-Wise',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig
```

### Category Wise
    
```{r}
data<-superstore %>% group_by(Category) %>% summarise(N=n())
fig <- plot_ly(data, labels = ~Category, values = ~N, type = 'pie',
               textposition = 'inside',
        textinfo = 'label+percent',
        insidetextfont = list(color = '#FFFFFF'),
        hoverinfo = 'text',
        text = ~paste( N, ' Sales'),showlegend = FALSE,
        marker=list(line = list(color = '#FFFFFF', width = 1)))

fig <- fig %>% layout(title = '# of Sales Category-Wise',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig
```

Sales(Amount) {data-orientation=rows}
===================================== 


Row {data-width=650} 
-------------------------------------
### State-Wise Division
```{r}
t<-superstore %>% group_by(State) %>% summarise(N=sum(Sales)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$State[1:6],
    y = t$N[1:6],
    type = 'bar',
    text = t$State[1:6],
    marker=list(color=c("red","red","silver","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'State-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'State'
    ),
    yaxis = list(
      title = '$ in Sales'
    )
  ) 

fig 
```

Row {.tabset .tabset-fade} 
-------------------------------------

### Category Wise
```{r}
data<-superstore %>% group_by(Category) %>% summarise(N=sum(Sales))
fig <- plot_ly(data, labels = ~Category, values = ~N, type = 'pie',
               textposition = 'inside',
        textinfo = 'label+percent',
        insidetextfont = list(color = '#FFFFFF'),
        hoverinfo = 'text',
        text = ~paste( "$",N, 'in Sales'),showlegend = FALSE,
        marker=list(line = list(color = '#FFFFFF', width = 1)))

fig <- fig %>% layout(title = 'Amount of Sale Category Wise',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig
```

### City Wise

```{r}
t<-superstore %>% group_by(City) %>% summarise(N=sum(Sales)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$City[1:6],
    y = t$N[1:6],
    type = 'bar',
    text = t$State[1:6],
    marker=list(color=c("red","red","silver","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'City-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'City'
    ),
    yaxis = list(
      title = '# of Sales'
    )
  ) 

fig 
```   
 
### Region Wise
    
```{r}
t<-superstore %>% group_by(Region) %>% summarise(N=sum(Sales)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$Region,
    y = t$N,
    type = 'bar',
    text = t$Region,
    marker=list(color=c("red","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'Region-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'Region'
    ),
    yaxis = list(
      title = '$ of Sales'
    )
  ) 

fig 
```

### Ship-Mode Wise
    
```{r}
t<-superstore %>% group_by(`Ship Mode`) %>% summarise(N=sum(Sales)) %>% arrange(desc(N))
fig <- plot_ly(
    x =t$`Ship Mode`,
    y = t$N,
    type = 'bar',
    text = t$`Ship Mode`,
    marker=list(color=c("red","silver","silver","silver"))
  )
fig <- fig %>% layout(
    title = 'Ship_Mode-Wise Sale Distribution',
    xaxis = list(
      type = 'category',
      title = 'Ship Mode'
    ),
    yaxis = list(
      title = '$ of Sales'
    )
  ) 

fig 
```

### Segment Wise
    
```{r}
data<-superstore %>% group_by(Segment) %>% summarise(N=sum(Sales))
fig <- plot_ly(data, labels = ~Segment, values = ~N, type = 'pie',
               textposition = 'inside',
        textinfo = 'label+percent',
        insidetextfont = list(color = '#FFFFFF'),
        hoverinfo = 'text',
        text = ~paste( N, '$ in Sales'),showlegend = FALSE,
        marker=list(line = list(color = '#FFFFFF', width = 1)))

fig <- fig %>% layout(title = '$ of Sales Segment-Wise',
         xaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE),
         yaxis = list(showgrid = FALSE, zeroline = FALSE, showticklabels = FALSE))

fig
```

Conclusion {data-orientation=rows}
===================================== 
**Conclusion**
We see the following Trends in the data:
States with higher sales have lower Percentage of Discounts 
States with higher Discount Rates are actually losing the Store money

**Suggestion:**
Increase Prices in States with Discount % more than 0.1
Most States with high sales don't have any discounts applied, following this the top 10 sale states should have 0 Discounts to bring in more profit.

Our most valuable Category is Office supplies in the high Sales states, thus we should increase stock and prices of this category in the same states 

California and New York are the two most important locations for this store, it would be advised to focus their attention and expand in these two location as these have the most promise.